package Dao;

import JavaBean.Book;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
public class booksDAO {

    public int insert(Book books, String bookname) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int i  = 0;
        try {
            conn = DBGet.getConnection();
            pstmt = conn.prepareStatement("select * from books where bookname = '" + bookname + "'");
            rs = pstmt.executeQuery();
            if(rs.next()){
                System.out.println("修改失败，书本已存在");
                i = 0;
            }else{
                String sql = "Insert into books(bookname,author,publish,isbn,price,bookresume,publishdate) values(?,?,?,?,?,?,?)";
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1,bookname);
                pstmt.setString(2,books.getAuthor());
                pstmt.setString(3,books.getPublish());
                pstmt.setString(4,books.getIsbn());
                pstmt.setDouble(5,books.getPrice());
                pstmt.setString(6,books.getBookresume());
                pstmt.setString(7,books.getPublishdate());
                i = pstmt.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBGet.close(rs,pstmt,conn);
        }
        return  i;
    }


    public int update(String bookname,String author,String publish,String isbn,float price,String bookresume,String publishdate,String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int i = 0;
        try {
            conn = DBGet.getConnection();
            pstmt = conn.prepareStatement("select * from books where bookname = '" + bookname + "'");
            rs = pstmt.executeQuery();
            if(rs.next()&& ((rs.getInt(1))!=Integer.parseInt(id))){
                System.out.println("修改失败，书本已存在");
                i = 0;
            }
            else {
                String sql = "update books set bookname=?,author=?,publish=?,isbn=?,price=?,bookresume=?,publishdate=? where id=?";
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, bookname);
                pstmt.setString(2, author);
                pstmt.setString(3, publish );
                pstmt.setString(4, isbn);
                pstmt.setFloat(5, price);
                pstmt.setString(6, bookresume);
                pstmt.setString(7, publishdate);
                pstmt.setInt(8, Integer.parseInt(id));

                i = pstmt.executeUpdate();
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DBGet.close(rs,pstmt,conn);
        }
        return i;
    }

    public Book getAllBooksByID(String id){
        Book books= null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBGet.getConnection();
            pstmt = conn.prepareStatement("select * from books where id=?");
            pstmt.setInt(1,Integer.parseInt(id));
            rs = pstmt.executeQuery();
            // 展开结果集数据库
            if(rs != null && rs.next()) {
                books = new Book();
                // 通过字段检索
                books.setId(rs.getInt(1));
                books.setName(rs.getString(2));
                books.setAuthor(rs.getString(3));
                books.setIsbn(rs.getString(4));
                books.setPublish(rs.getString(5));
                books.setPrice(rs.getFloat(6));
                books.setBookresume(rs.getString(7));
                books.setPublishdate(rs.getString(8));


            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBGet.close(rs,pstmt,conn);
        }
        return books;
    }


    public ArrayList<Book> getAllBooks(){
        ArrayList<Book> list = new ArrayList<Book>();
        Book books = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBGet.getConnection();
            pstmt = conn.prepareStatement("select * from books");
            rs = pstmt.executeQuery();
            // 展开结果集数据库
            while (rs != null && rs.next()) {
                books = new Book();
                // 通过字段检索
                books.setId(rs.getInt(1));
                books.setName(rs.getString(2));
                books.setAuthor(rs.getString(3));
                books.setIsbn(rs.getString(4));
                books.setPublish(rs.getString(5));
                books.setPrice(rs.getFloat(6));
                books.setBookresume(rs.getString(7));
                books.setPublishdate(rs.getString(8));
                list.add(books);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBGet.close(rs,pstmt,conn);
        }
        return list;
    }



    public int delete(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int i = 0;
        try {
            conn = DBGet.getConnection();
            // 创建语句对象
            String sql = "delete from books where id=?";
            pstmt = conn.prepareStatement(sql);
            // 设置参数值
            pstmt.setInt(1, Integer.parseInt(id));
            // 执行删除数据
            i = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBGet.close(pstmt,conn);
        }
        return i;
    }
}
